<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
  <script src="http://www.google-analytics.com/urchin.js" type="text/javascript"></script>
  <script type="text/javascript">
    _uacct = "UA-2543462-4";
    urchinTracker();
  </script>
  <meta http-equiv="Content-Type" content="text/html; charset=UTF-8"/>
  <meta name="Author" content="felipe@fratech.net"/>
  <meta name="Description" content="BoxSQL - Fast SQL for Java"/>
  <meta name="Keywords" content="BoxSQL SQL template java persistence open source opensource Fratech visão ágil
    flexível flexibilidade framework persistência dados DAO Data DB Banco de dados tutorial"/>
  <meta name="Language" content="pt-br"/>
  <meta name="doc-class" content="Completed"/>
  <meta name="doc-rights" content="Public"/>
  <meta name="COPYRIGHT" content="Copyright (c) by BoxSQL Team"/>
  <meta name="ROBOTS" content="INDEX, FOLLOW"/>
  <meta name="REVISIT-AFTER" content="1 DAYS"/>
  <meta http-equiv="EXPIRES" content="0"/>
  <meta name="RESOURCE-TYPE" content="DOCUMENT"/>
  <meta name="DISTRIBUTION" content="GLOBAL"/>
  <meta http-equiv="Pragma" content="no-cache"/>
  <link rel="stylesheet" href="css/style.css" type="text/css"/>
  <title>.: BoxSQL :.</title>
</head>
<body>

<div align="center">
<img src="images/boxSQL.gif" alt="BoxSQL"/>
<br/>
<h1 style="font-size:24pt; font-family:Courier New, Courier, monospace; color:black; font-weight: bold;">Fast SQL for Java</h1>
</div>
<h2>Description</h2>
<p>
BoxSQL is simple and efficient framework open-source that to organize used code SQL of a Java application inside. 
Characteristics:
</p>
<ul class="description">
	<li>Remove instructions SQL of the Java code;</li>
	<li>Archives SQL as templates;</li>
	<li>Archives SQL as resources in packages;</li>
	<li>Fast Reading of archives SQL;</li>
	<li>Automatic Mapping of the parameters;</li>
	<li>Cache of templates read;</li>
	<li>Automatic Log of processing;</li>
	<li>To process instructions SQL in the SGDB and automatically to return lists from objects or total of affected registers;</li>
	<li>Execution of SQL based on the efficiency and stability of the Apache DbUtils; </li>
</ul>
<h2>Staff</h2>

<ul class='staff'>
	<li>Manoel Pimentel (Leader) - manoelp[at]dev.java.net; </li>
	<li>Felipe Rodrigues (Architect) - feliperod[at]dev.java.net; </li>
	<li>Luis Fernando Diniz de Andrade (Developer) - luisfernando[at]dev.java.net; </li>
</ul>
<br/><br/>
<h2>Architecture</h2>

<div align="center"><img src="images/arquiteturaProposta.png" width="900" height="500" alt="BoxSQL Overview"/></div>
<br/><br/>
<h2>Automatic mapping of parameters </h2>
<div align="center"><img src="images/mapeamentoAutomatico.JPG" width="900" height="450" alt="BoxSQL Object Relational Mapping"/></div>
<br/><br/>
<h2>Automatic mapping of results </h2>
<div align="center"><img src="images/mapeamentoDeResultados.JPG" alt="BoxSQL - Results Mapping"/></div>
<br/><br/>
<h2>BoxSQL in your application – Step-by-step</h2>
<br/><br/>
<h3>Step 1 - Structures of the project and files JARs</h3>
<p><i>*Just structure of classes and files</i></p>
<div align="center"><img src="images/diretorios_com_properties.jpg" alt="BoxSQL - Structure"/></div>
<br/><br/>
<h3>Step 2 - Creating file properties</h3>

<textarea rows="10" cols="121" readonly="readonly" style="background-color: #F3F3F3;width: 100%">
box.connectionType=jdbc
jndi.resourceName=
box.pathBase=/org/exemplo/dao/sql
log.path=c:\\boxSQL.log
log.active=false
jdbc.url=jdbc:firebirdsql:localhost:c:\\ACADEMICO.FDB
jdbc.driver=org.firebirdsql.jdbc.FBDriver
jdbc.user=sysdba
jdbc.password=masterkey
</textarea>
<br/><br/>
<h3>Step 3 - Creating the VO class</h3>

<textarea rows="15" cols="121" readonly="readonly" style="background-color: #F3F3F3;width: 100%">
package org.exemplo.vo;

public class ProdutoVO {
    private String ultimaCompra;
    private int codPrd;
    private String nome;
    private double preco;
    private String nivel;

    public ProdutoVO() {
    }

   //Methods getters and setters
}
</textarea>
<br/><br/>
<h3>Step 4 - Creating the templates files</h3>
<p><i>*All to place the files in the package org.exemplo.dao.sql</i></p>
<h4>4.1 – alteraProduto.sql ( For Updates)</h4>
<textarea rows="7" cols="121" readonly="readonly" style="background-color: #F3F3F3;width: 100%">
update PRODUTOS 
set
   NOME=':NOME',
   PRECO_VENDA=:PRECO
where
   CODPRD=:CODPRD
</textarea>
<h4>4.2 – produto.sql ( For selection )</h4>
<textarea rows="15" cols="121" readonly="readonly" style="background-color: #F3F3F3;width: 100%">
select
    PR.ID as codPrd,
    upper(PR.NAME) as nome,
    coalesce(PR.PRICE_SALES,0) as preco,
    extract(MONTH from PR.DATE_LAST_PURCHASE) as ultimaCompra,
    case
        when coalesce(PR.PRICE_SALES,0) <= 20 then  'BAIXO'
        when coalesce(PR.PRICE_SALES,0) <= 30 then  'MÉDIO'
        else 'ALTO'
    end as nivel
from 
    PRODUCTS  PR
order by
    upper(PR.NAME)
</textarea>
<h4>4.3 –  produtoPorID.sql ( For selection with criterion)</h4>
<textarea rows="15" cols="121" readonly="readonly" style="background-color: #F3F3F3;width: 100%">
select
    PR.ID as codPrd,
    upper(PR.NAME) as nome,
    coalesce(PR.PRICE_SALES,0) as preco,
    extract(MONTH from PR.DATE_LAST_PURCHASE) as ultimaCompra,
    case
        when coalesce(PR.PRICE_SALES,0) <= 20 then  'BAIXO'
        when coalesce(PR.PRICE_SALES,0) <= 30 then  'MÉDIO'
        else 'ALTO'
    end as nivel
from 
    PRODUCTS  PR
where
   PR.ID=:CODPRD
</textarea>
<br/><br/>
<h3>Step 5  - Creating  DAO  class </h3>
<p><i>*Place that class in the package org.exemplo.dao</i></p>
<textarea rows="30" cols="121" readonly="readonly" style="background-color: #F3F3F3; width: 100%">
package org.exemplo.dao;

import java.util.List;
import org.boxsql.BoxSQL;
import org.exemplo.vo.ProdutoVO;

public class  ProdutoDAO {
    
    
    /**
     * Method to supply a collection of objects with base a simple selection of lines
     * @return List
     */
    public  List<ProdutoVO> getLista(){
        BoxSQL box = new BoxSQL();
        List listaProdutos;
        try {
            listaProdutos = box.getList("produtos/produto.sql", ProdutoVO.class);
        } finally {
            box.release();
        }
        return listaProdutos;
    }
    
    
    /**
     * Method to supply an only objects with base in the selection of lines with criteria
     * @param idPrd id for selection
     * @return ProdutoVO
     */
    public  ProdutoVO getObjetoPorId(int idPrd){
        BoxSQL box = new BoxSQL();
        box.setParameter("CODPRD",idPrd);
        ProdutoVO produtosBeam;
        try {
            produtosBeam = (ProdutoVO) box.getObject("produtos/produtoPorID.sql", ProdutoVO.class);
        } finally {
            box.release();
        }
        return produtosBeam;
    }
    
    /**
     * Method to alter the values of the fields of a certain registration
     * @param prd object that will be persisted
     * @return int with the number of affected registrations
     */
    public  int altera(ProdutoVO prd){
        BoxSQL box = new BoxSQL();
        int totalAfetado;
        try {
            totalAfetado = box.executeUpdate("produtos/alteraProduto.sql", prd);
            
        } finally {
            box.release();
        }
        return  totalAfetado;
    }
}
</textarea>
<br/><br/>
<h3>Step 6  - Creating  BO  class </h3>
<p><i>*Place that class in the package  org.exemplo.bo</i></p>
<textarea rows="30" cols="121" readonly="readonly" style="background-color: #F3F3F3;width: 100%">
package org.exemplo.bo;

import java.util.List;
import org.exemplo.vo.ProdutoVO;
import org.exemplo.dao.ProdutoDAO;


public class ProdutoBO {
    
    private ProdutoDAO prdDAO = new ProdutoDAO();
    private List<ProdutoVO> lista = prdDAO.getLista();
    private int codPrd;

  
    public double getTotalGeral() {
        double totalGeral = 0;
        for (ProdutoVO elem : lista) {
            totalGeral += elem.getPreco();
        }
        return totalGeral;
    }
    
    public int getTotalRegistro(){
        return lista.size();
    }
    
    public List<ProdutoVO> getLista(){
        return prdDAO.getLista();
    }
    
    public void setCodPrd(int codPrd){
        this.codPrd = codPrd;
    }
    
     public  ProdutoVO getObjetoPorId(){
         return prdDAO.getObjetoPorId(this.codPrd);
     }

     public int alteraProduto(ProdutoVO prd){
        return  prdDAO.altera(prd);
    }
    
}

</textarea>
<br/><br/>
<h3>Step 7  - Fragment of a page JSP  with JSTL,  to show a listing of the objects </h3>
<p><i>*In case it is an application web</i></p>
<textarea rows="10" cols="121"   readonly="readonly" style="background-color: #F3F3F3;width: 100%">
<jsp:useBean id="prodBO" scope="page" class="org.exemplo.bo.ProdutoBO"/>

  <c:forEach var="produto" items="${prodBO.lista}">
       <tr>                 
             <td><c:out value="${produto.codPrd}"/> </td>
             <td><a href="mostra.jsp?CODPRD=${produto.codPrd}"><c:out value="${produto.nome}"/></a></td>
             <td><fmt:formatNumber value="${produto.preco}" type="  currency" maxFractionDigits="2" /> </td>
             <td><c:out value="${produto.nivel}"/>  </td>
        </tr>
 </c:forEach>
</textarea>
<br/><br/>
<h2>Sponsors</h2>
<div align="center">
<a href="http://www.visaoagil.com" target="_new" ><img src="images/LogoRevistaVisaoAgil_02.png" width="400" height="100"/></a>
<a href="http://www.fratech.net" target="_new" ><img src="images/fratech_transp.gif" width="400" height="100"/></a>
</div>
</body>  
</html>